package com.css.casualties.mapper.pop.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.css.entity.DataPop;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author Dbj
 * @date 2024年09月18日  13:47
 */
@Mapper
public interface PopGridMapper extends BaseMapper<DataPop> {
    @Select("select population,geom from data_pop where ST_Intersects(geom,st_geometryfromtext(#{geom},#{code}))")
    List<DataPop> getByGeom(@Param("geom") String geom,@Param("code") String code);
    @Select("WITH sjz AS ( SELECT ST_GeomFromText ( #{geom}, #{code} ) AS geom ) SELECT SUM( ( P ).val ) AS population FROM " +
            " data_pop_raster r JOIN sjz s ON ST_Intersects ( r.geom, s.geom ) CROSS JOIN LATERAL ST_PixelAsPolygons ( ST_Clip ( r.geom, s.geom ) ) AS P " +
            "WHERE ( P ).val >= 0")
    DataPop getByGeom1(@Param("geom") String geom,@Param("code") String code);
}
